In database systems, a “transaction” refers to an atomic set of operations performed against a database, which may access, create, modify or delete database data or metadata. A “commit” occurs when the transaction has completed its processing and any changes to the database by the transaction are ready to be permanently implemented in the database system.
Transaction log records can be maintained in a database system to allow data recovery in the event of an error, that may include hardware failure, network failure, process failure, database instance failure, data access conflicts, user errors, and statement failures in database access programs.
Various types of transaction log records can be maintained in a database system for data recovery. One type of log record that may be maintained is the “undo” record. Undo records contain information about changes that were introduced into the database system. For example, if a row in a table were modified, the changes will be stored in the undo record identifying the block of the database system that includes the modified table row.
Memory or disk space needs to be allocated for storage of undo records. Database managers usually set the undo tablespace size by predicting how many undo records may be generated. Often there is not enough statistical information available for database administrators to use in order to arrive at an accurate prediction of undo records generation. Incorrect undo tablespace size may cause errors in the system, as not enough undo records may be available. Alternatively, allocating too much memory or disk space for storing undo records is inefficient.
Moreover, database administrators need to predict how long undo records should be maintained, a parameter known as the “undo retention.” Users may require older versions of the data for various reasons. In order to prevent these users from obtaining error messages, undo records should be maintained in the system to allow the data to be retained to their previous values. However, undo tablespace is limited, and new transactions require undo tablespace. A user may therefore monitor the system activity and adjust the undo retention based on the amount of undo information generated by new transactions.
However, database system activity levels may fluctuate; for example, activity levels may be higher during regular business hours than at night. Adjusting the parameters to the fluctuation system activity may therefore require a user to constantly monitor system activity and change the parameters as needed.
What is needed, therefore, is a solution that overcomes these and other shortcomings of the prior art.